Page History: DDE - Getting data into Microsoft Excel
Compare Page Revisions
Page Revision: 2012/01/26 07:46
T4 supports sending data via DDE to other applications, such as Microsoft Excel.
To send quote data you need to have an open Quote Board window containing the markets that you want to see, then click the ‘DDE’ button and go to Excel. Right click on a cell in Excel and choose the ‘Paste’ menu item. This will paste a table of links to T4 that contain the same markets and columns as your Quote Board. Alternatively, you can right click on a single value in the Quote Board and select the ‘Copy DDE Link’ menu item, then paste into Excel and only that value will be linked.
Note: For Excel to get quotes the Market MUST be displayed in the T4 frontend somewhere, either in a Quote Board or contract window BEFORE it is linked to from Excel. This also applies if you are loading a saved Excel spreadsheet.
The DDE link format for market data in Excel is:
=T4Screen|MKT_
!
Where:
is the unique identifier for the market in T4. This does not match any exchange published symbol. You can only obtain these by using the above method of copying the link for a value from T4 and pasting it into Excel or some other application.
is the data value of the market that you want, for example last trade price. The valid values are shown below:
|
| Value | Meaning |
|
| Description | The name of the market. |
|
| Numerator | The smallest amount that the price of a market can move by, in ticks. |
|
| Denominator | The denominator used for converting ticks into price and vice-versa. |
|
| Settlement_Price,Settlement_Ticks | The last settlement price received. |
|
| Open_Price,Open_Ticks | The opening trade price for the day. |
|
| High_Price,High_Ticks | The highest traded price for the day. |
|
| Low_Price,Low_Ticks | The lowest traded price for the day. |
|
| Mode | The current market state, e.g. PreOpen, Open, Closed etc. |
|
| Total_Traded_Volume | The total volume traded by this market today. |
|
| Last_Trade_Volume | The last volume traded at the current price. If the frontend is set to show the total traded at this price since the last price changed then that value will be sent, otherwise the volume just traded will be sent. |
|
| Last_Trade_Price,Last_Trade_Ticks | The price of the last trade to occur. |
|
| Bid_Volume | The volume of the current best bid. |
|
| Bid_Price,Bid_Ticks | The price of the current best bid. |
|
| Offer_Volume | The volume of the current best offer. |
|
| Offer_Price,Offer_Ticks | The price of the current best offer. |
|
| Net_Change_Price,Net_Change_Ticks | The net change from the last settlement price to the last trade price. |
|
The last trade information will include trades in outrights due to spreads trading if the frontend is configured to show that information itself.
The Bid and Offer information will include implied prices if the frontend is configured to show it.
The difference between the ‘_TICKS’ and ‘_PRICE’ values is that the ‘_PRICE’ values are display prices (what you see on the frontend, e.g. 30yr bond = 11215) and the ‘_TICKS’ values are the internal equivalent that can have math done on it (e.g. 30yr bond = 11215 = 112 points + 15/32 = expressed as a total number of 32nds is (112 * 32) + 15 = 3599).
To send account and position data you can use the same method as above, except using the Account window instead of the Quote Board. Note that if you trade in a new market then Excel will not automatically detect that position, you must place links in Excel for each specific position that you want.
The DDE link format for account data in Excel is:
=T4Screen|ACT_!
Where:
is the unique identifier for the account in T4. This does not match the account number or name. You can only obtain these by using the above method of copying the link for a value from T4 and pasting it into Excel or some other application.
is the data value of the account that you want, for example P&L. The valid values are shown below:
|
| Value | Meaning |
|
| Description | The name of the account. |
|
| Account | The account number. |
|
| Firm | The firm this account belongs to. |
|
| Status | The status of this account, e.g. OK, Blocked etc. |
|
| PL | The total P&L of all the positions for this account |
|
| UPL | The total unrealized P&L of all the positions for this account. |
|
| RPL | The total realized P&L of all the positions for this account. |
|
| Overnight_UPL | The total unrealized P&L due to fills carried over from the previous trading day of all the positions for this account. |
|
| Cash | The amount of available cash the account has available to trade with. Takes into consideration the Balance, P&L and Margin. |
|
| Net_Equity | The balance of the account if all positions were closed. Takes into consideration the Balance and P&L. |
|
| Margin | The total margin requirement for all the positions for this account. |
|
| Balance | The start of day balance of the account. |
|
The DDE link format for position data in Excel is:
=T4Screen|POS__!
Where:
is the unique identifier for the account in T4. This does not match the account number or name. You can only obtain these by using the above method of copying the link for a value from T4 and pasting it into Excel or some other application.
is the unique identifier for the market in T4. This does not match any exchange published symbol. You can only obtain these by using the above method of copying the link for a value from T4 and pasting it into Excel or some other application.
is the data value of the position that you want, for example P&L or net position. The valid values are shown below: